--8.NV duyet lenh chuyen tien ngoai NH, trong khi do KH va xem chi tiet GD
if exists (select name from sysobjects where name like 'Proc_GDNgoaiNH_Update')
	drop procedure Proc_GDNgoaiNH_Update
go

create PROC Proc_GDNgoaiNH_Update (@maGD int,@trangthai int,@type int)
as
begin
	if(@type=1)--dirty
	begin
		begin tran
			Update GiaoDich 
			set TrangThaiGD=@trangthai
			where MaGD=@maGD
		waitfor delay '00:00:05'
		rollback tran
	end
	else if(@type=2)--phantom
	begin
		select * from GiaoDich
	end
	else if(@type=3)--unrepeatable
	begin
		begin tran
			Update GiaoDich 
			set TrangThaiGD=@trangthai
			where MaGD=@maGD
		commit tran
	end
end
go
---------------
if exists (select name from sysobjects where name like 'Proc_AllGD_ByID')
	drop proc Proc_AllGD_ByID
go

create proc Proc_AllGD_ByID (@maTKThe int,@type int)
as
begin
	if(@type=1)--dirty
	begin
		begin tran
		set tran isolation level read uncommitted
			select * from GiaoDich where MaTKThe=@maTKThe 
		commit tran
	end
	else if(@type=2)--phantom
	begin
		begin tran
		set tran isolation level repeatable read
			select * from GiaoDich where MaTKThe=@maTKThe
			waitfor delay '00:00:05'
			select * from GiaoDich where MaTKThe=@maTKThe
		commit tran
	end
	else if(@type=3)--unrepeatable
	begin
		begin tran
			--select * from GiaoDich where MaTKThe=@maTKThe
			waitfor delay '00:00:05'
			select * from GiaoDich where MaTKThe=@maTKThe
		commit tran
	end
end
go
---------------------------
if exists (select name from sysobjects where name like 'Proc_GiaoDich_ChuyenTienNgoaiNH')
	drop procedure Proc_GiaoDich_ChuyenTienNgoaiNH
go

create PROC Proc_GiaoDich_ChuyenTienNgoaiNH (@maTKThe int ,@loaiGD int , @ngayGD datetime ,
	 @diaChi nvarchar (50), @soTienGD float , @noiDung nvarchar,@MaTKNhan int,@MaNganHang int,@maNV int, @trangThai int,@type int)
as
begin
	if(@type=1)--dirty
	begin
		begin tran
			update TaiKhoanThe set SoDu = SoDu-@soTienGD where MaTaiKhoanThe=@maTKThe
			insert into GiaoDich(MaTKThe,LoaiGD,NgayGD,DiaChiGD,SoTienGD,NoiDungGD, MaTKNhan,MaNganHang,MaNVGD,TrangThaiGD)
			values (@maTKThe ,@loaiGD  , @ngayGD , @diaChi , @soTienGD  , @noiDung,@MaTKNhan,@MaNganHang, @maNV, @trangThai) 
		waitfor delay '00:00:05'
		rollback tran
	end
	else if(@type=2)--phantom
	begin
		begin tran
			update TaiKhoanThe set SoDu = SoDu-@soTienGD where MaTaiKhoanThe=@maTKThe
			insert into GiaoDich(MaTKThe,LoaiGD,NgayGD,DiaChiGD,SoTienGD,NoiDungGD, MaTKNhan,MaNganHang,MaNVGD,TrangThaiGD)
			values (@maTKThe ,@loaiGD  , @ngayGD , @diaChi , @soTienGD  , @noiDung,@MaTKNhan,@MaNganHang, @maNV, @trangThai) 
		commit tran
	end

end
go
---------------------------
if exists (select name from sysobjects where name like 'Proc_GiaoDich_ChuyenTienTrongNH')
	drop procedure Proc_GiaoDich_ChuyenTienTrongNH
go

create PROC Proc_GiaoDich_ChuyenTienTrongNH (@maTKThe int ,@loaiGD int , @ngayGD datetime ,
	 @diaChi nvarchar (50), @soTienGD float , @noiDung nvarchar,@MaTKNhan int,@maNV int, @trangThai int,@type int)
as
begin
	if(@type=1)--dirty
	begin
		begin tran
			update TaiKhoanThe set SoDu = SoDu-@soTienGD where MaTaiKhoanThe=@maTKThe
			update TaiKhoanThe set SoDu = SoDu+@soTienGD where MaTaiKhoanThe=@MaTKNhan
			insert into GiaoDich(MaTKThe,LoaiGD,NgayGD,DiaChiGD,SoTienGD,NoiDungGD,MaTKNhan,MaNVGD,TrangThaiGD)
			values (@maTKThe ,@loaiGD  , @ngayGD , @diaChi , @soTienGD, @noiDung,@MaTKNhan,@maNV, @trangThai) 
		waitfor delay '00:00:05'
		rollback tran
	end
	else if(@type=2)--phantom
	begin
		begin tran
			update TaiKhoanThe set SoDu = SoDu-@soTienGD where MaTaiKhoanThe=@maTKThe
			update TaiKhoanThe set SoDu = SoDu+@soTienGD where MaTaiKhoanThe=@MaTKNhan
			insert into GiaoDich(MaTKThe,LoaiGD,NgayGD,DiaChiGD,SoTienGD,NoiDungGD,MaTKNhan,MaNVGD,TrangThaiGD)
			values (@maTKThe ,@loaiGD  , @ngayGD , @diaChi , @soTienGD, @noiDung,@MaTKNhan,@maNV, @trangThai) 
		commit tran
	end
	else if(@type=3)--unrepeatable
	begin
		select * from GiaoDich
	end
end
go
--------------
if exists (select name from sysobjects where name like 'Proc_NganHang_GetAll')
	drop procedure Proc_NganHang_GetAll
go
create PROC Proc_NganHang_GetAll
as
begin
	 select * from NganHang where TrangThai=0
end
go
--------
if exists (select name from sysobjects where name like 'Proc_AllGD_ChuyenTienNgoaiNH')
	drop procedure Proc_AllGD_ChuyenTienNgoaiNH
go
create PROC Proc_AllGD_ChuyenTienNgoaiNH
as
begin
	 select * from GiaoDich where TrangThaiGD = -1
end
go
